/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.*;
import entities.*;
import java.security.acl.Group;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author lion
 */
public class FacultyDao {

    private Connection conn = null;
    private Statement stm = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;
    MysqlHelper mysql = new MysqlHelper();

    public void addFaculty(Faculty faculty, int memberid) throws Exception {
        try {
            conn = mysql.GetConnection();
            ps = conn.prepareStatement("INSERT INTO fl_faculty VALUES(DEFAULT,?,?,?,?)");
            ps.setInt(1, faculty.getCourseid());
            ps.setInt(2, memberid);
            ps.setString(3, faculty.getName());
            ps.setString(4, faculty.getAddress());
            ps.executeUpdate();

            GroupsDao groupsDao = new GroupsDao();
            Groups groups = new Groups();
            groups.setGroupname("Faculty");
            int groupid = groupsDao.getGroupId(groups);
            mysql.ExecuteUpdate("UPDATE fl_membergroup SET groupid = " + groupid
                    + " WHERE memberid = " + memberid);
        } catch (Exception e) {
            throw e;
        }
    }

    public void editFaculty(Faculty faculty) throws Exception {
        try {
            conn = mysql.GetConnection();
            mysql.ExecuteUpdate("UPDATE fl_faculty SET courseid='" + faculty.getCourseid()
                    + "' WHERE facultyid=" + faculty.getFacultyid());
            mysql.ExecuteUpdate("UPDATE fl_faculty SET name='" + faculty.getName()
                    + "' WHERE facultyid=" + faculty.getFacultyid());
            mysql.ExecuteUpdate("UPDATE fl_faculty SET address='" + faculty.getAddress()
                    + "' WHERE facultyid=" + faculty.getFacultyid());
        } catch (Exception e) {
            throw e;
        }
    }

    public ArrayList<Faculty> getFacultyList() throws Exception {
        ArrayList<Faculty> listFacultys = new ArrayList<Faculty>();
        conn = mysql.GetConnection();
        rs = mysql.ExecuteReader("SELECT * FROM fl_faculty");
        while (rs.next()) {
            Faculty faculty = new Faculty();
            faculty.setFacultyid(rs.getInt("facultyid"));
            faculty.setMemberid(rs.getInt("memberid"));
            faculty.setCourseid(rs.getInt("courseid"));
            faculty.setName(rs.getString("name"));
            faculty.setAddress(rs.getString("address"));
            listFacultys.add(faculty);
        }
        return listFacultys;
    }

//    public <T extends Object> List<T> getList(T t, String table) throws Exception{
//        List<T> list = new ArrayList<T>();
//        conn = mysql.GetConnection();
//        rs = mysql.ExecuteReader("SELECT * FROM " + table);
//        while () {            
//            list.add(t);
//        }
//        return list;
//    }
    public boolean checkExist(int memberid) throws Exception {
        boolean flag = false;
        try {
            conn = mysql.GetConnection();
            ps = conn.prepareStatement("SELECT * FROM fl_faculty WHERE memberid=?");
            ps.setInt(1, memberid);
            rs = ps.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (Exception e) {
            throw e;
        }
        return flag;
    }

    public List<Faculty> findFaculty(String id) {
        List<Faculty> faculty = new ArrayList<Faculty>();
        try {
            conn = mysql.GetConnection();
            rs = mysql.ExecuteReader("SELECT * FROM fl_faculty WHERE " + id);
            while (rs.next()) {                
                Faculty f = new Faculty();
                f.setFacultyid(rs.getInt("facultyid"));
                f.setAddress(rs.getString("address"));
                f.setName(rs.getString("name"));
                f.setCourseid(rs.getInt("courseid"));
                f.setMemberid(rs.getInt("memberid"));
                faculty.add(f); 
            }
        } catch (Exception e) {
        }
        return faculty;
    }
}
